package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.utils.JDBCUtils;

import com.bean.*;

public class PassageDao {
	FilterPassageCondition fpc = new FilterPassageCondition();
	
	//未使用表单筛选时，city = default,mincost = 0,maxcost = (int)Float.POSITIVE_INFINITY,
	//minday = 0,maxday = (int)Float.POSITIVE_INFINITY;
	//拼接SQL语句
	//给原有的SQL语句加上and条件
    public String FilterSQL(StringBuilder sql)
    {
      if(fpc.city !="default"){
    	  sql.append(" and city LIKE '"+fpc.city+"'");
      }
      sql.append("   and cost BETWEEN "+fpc.mincost+" AND "+fpc.maxcost)
         .append("   and days BETWEEN "+fpc.minday+" AND "+fpc.maxday)
      ;
      return sql.toString();
    }
	
	
/*	//筛选Passage
	public boolean FilterPassage(String city, int mincost,int maxcost,int minday,int maxday) 
	{
		//是否筛选成功
		boolean flag = false;
		
		try {
		Connection conn = JDBCUtils.getConnection();
    	ResultSet rs = null;
        StringBuilder sql1=new StringBuilder()
                .append("select * from passage")
                .append("   where 1=1")
        ;
    	String sql = FilterSQL(sql1);
        PreparedStatement ps =  conn.prepareStatement(sql);
		int i =  ps.executeUpdate();			
		rs = ps.executeQuery(sql.toString());
		if(i>0){
			flag = true;
		}
		JDBCUtils.close(rs);
		JDBCUtils.close(ps);
		conn.close();
		} catch (Exception e) {
			System.out.println("sql筛选异常");
			e.printStackTrace();
		}

		return flag;
	}*/
    //用于返回passage的编号
    //如果返回0，发生错误
    public int returnPassageID(String shareTitle)
            throws Exception,ClassNotFoundException, IllegalAccessException,
            InstantiationException, SQLException
    {
    	Connection conn = JDBCUtils.getConnection();
    	Statement stmt = conn.createStatement();
    	ResultSet rs = null;
	
        StringBuilder selectSQL =new StringBuilder()
        		.append("SELECT passageID FROM passage where ")
        		.append("title ='"+shareTitle+"'")
        		;
        rs = stmt.executeQuery(selectSQL.toString());
        int getPassageID = 0;
        if(rs!=null&&rs.next()){

            getPassageID=rs.getInt("passageID");
            stmt.execute(selectSQL.toString());
        }
        return getPassageID;
    }

    //查询选择的passage
    public Passage queryPassage(int passageID)throws Exception
    {

    	Connection conn = null;
    	Statement stmt = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();

        StringBuilder sql = new StringBuilder()
        		.append("SELECT * FROM passage where passageID='" + passageID + "'")
        		;

        ResultSet rs = stmt.executeQuery(sql.toString());
        Passage passage = new Passage();

        try {
            while (rs.next()) {

                passage.setPassageID(rs.getInt("passageID"));
                passage.setTitle(rs.getString("title"));
                passage.setAuthorName(rs.getString("authorName"));
                passage.setContent(rs.getString("passageContent"));
                passage.setDate(rs.getString("passageDate"));
                passage.setCost(rs.getInt("cost"));
                passage.setDays(rs.getInt("days"));
                passage.setAuthorID(rs.getString("authorID"));
                passage.setAuthorID(rs.getString("authorID"));
                passage.setLike(rs.getInt("likeNum"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return passage;
    }

/*    ,ClassNotFoundException, IllegalAccessException
    , InstantiationException, SQLException*/
    //查询passage数量
    public int searchPassageNumber() throws Exception{
    	Connection conn = null;
    	Statement stmt = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();

        StringBuilder sqlNumber1 = new StringBuilder()
        		.append("SELECT COUNT(*) AS number FROM passage WHERE 1=1")
        ;     
        System.out.println(sqlNumber1);
        
        String sqlNumber = FilterSQL(sqlNumber1);
        System.out.println(sqlNumber);  
        ResultSet rs = stmt.executeQuery(sqlNumber);

        if(rs.next()) {
            int result=rs.getInt("number");
            stmt.close();
            conn.close();
            return result;
        }

        else {
            stmt.close();
            conn.close();
            return 0;
        }
    }

    //按照time的值返回passage列表
    public Passage searchPassage(Passage passage,int time) throws Exception,ClassNotFoundException,
            IllegalAccessException, InstantiationException, SQLException {


    	Connection conn = null;
    	Statement stmt = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
        
        StringBuilder sqlSelect1 =
        		new StringBuilder()
        		.append("SELECT * FROM passage WHERE 1=1")
        		;
        String  sqlSelect = FilterSQL(sqlSelect1);
        
        ResultSet result = stmt.executeQuery(sqlSelect.toString());

        for(int i=1;i<time;i++) {
            if (result != null)
                result.next();
        }
        if(result != null && result.next()) {
            passage.setAuthorID(result.getString("authorID"));
            String passageID = result.getString("passageID");
            passage.setPassageID(Integer.valueOf(passageID).intValue());
            passage.setTitle(result.getString("title"));
            passage.setLike(result.getInt("likeNum"));
            passage.setCost(result.getInt("cost"));
            passage.setDays(result.getInt("days"));
            passage.setAuthorName(result.getString("authorName"));
            passage.setDate(result.getString("passageDate"));
            passage.setCity(result.getString("city"));
        }

        /*result.close();
        stmt.close();
        conn.close();*/
        try {
        	if(result != null){
        		result.close();
        	}
        	if(stmt != null){
        		stmt.close();
        	}
        	if(conn != null){
        	conn.close();
        	}
        	} catch (Exception e) {
        	e.printStackTrace();
        	}
        return passage;
    }
}
